import pandas as pd
from openpyxl import load_workbook
import os

def get_sheet_num(path_read):
    # 加载 Excel 文件
    workbook = load_workbook(filename=path_read, read_only=True)

    # 获取所有页签名称列表
    sheet_names = workbook.sheetnames
    print(sheet_names)
    return len(sheet_names)


def write_to_csv(path_read,path_write):
    #页签为1个
    assert get_sheet_num(path_read) == 1


    df = pd.read_excel(path_read,index_col=None)
    filtered_columns = df.filter(like='知识点').columns.tolist()  # 模糊筛选列名
    df_filtered = df[filtered_columns]
    #不为nan的数量
    non_nan_count = df_filtered.count().sum()
    # 将所有列合并为一列
    merged_column = df_filtered.stack().reset_index(drop=True)
    assert non_nan_count == merged_column .shape[0]
    df_write = merged_column.str.split(';', expand=True).rename(columns={0: 'knowledge_name', 1: 'knowledge_code'})

    df_write.to_csv(path_write, sep = "\t",index=False,header=False)
    print(f"{path_write}写入完成")

if __name__ == '__main__':
    root_path_read = r"D:/blwang14/知识点体系"
    list_dir = os.listdir(root_path_read)
    for dir in list_dir:
        root_path_write = r"D:/blwang14/知识点体系new"
        #过滤缓存文件
        if not dir.startswith('~$'):
            name_without_extension = os.path.splitext(dir)[0]
            file_name = name_without_extension+".csv"
            path_read = os.path.join(root_path_read,dir)
            path_write = os.path.join(root_path_write,file_name)
            write_to_csv(path_read,path_write)
    




